﻿using System;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
using System.Data;
using LibHuman;

namespace Human
{
	public class Bieu01 : System.Windows.Forms.Form
	{
		private int m_userid=0;
        Language lan = new Language();
		private string s_tu,s_den,s_tu1,s_tu2,s_bophan="",user_dn="";	
		private LibHuman.AccessData m=new LibHuman.AccessData();
		private string s_thumuc="",sql="",user="";
		private System.Windows.Forms.DateTimePicker den;
		private System.Windows.Forms.DateTimePicker tu;
		private System.Windows.Forms.Label label3;
		private System.Windows.Forms.Label label2;
		private System.Windows.Forms.ComboBox ky;
		private System.Windows.Forms.NumericUpDown nam;
		private System.Windows.Forms.ComboBox loaibc;
		private System.Windows.Forms.Label label1;
        private System.Windows.Forms.Label label4;
        private Button btn_ok;
        private Button btn_thoat;
		private System.ComponentModel.Container components = null;

		public Bieu01(string s_makp,string s_userdn)
		{
			InitializeComponent();
            lan.Read_Language_to_Xml(this.Name.ToString(), this);
            lan.Changelanguage_to_English(this.Name.ToString(), this);
            s_bophan = s_makp;
            user_dn = s_userdn;
			
		}

		protected override void Dispose( bool disposing )
		{
			if( disposing )
			{
				if (components != null) 
				{
					components.Dispose();
				}
			}
			base.Dispose( disposing );
		}

		#region Windows Form Designer generated code
		/// <summary>
		/// Required method for Designer support - do not modify
		/// the contents of this method with the code editor.
		/// </summary>
		private void InitializeComponent()
		{
            System.ComponentModel.ComponentResourceManager resources = new System.ComponentModel.ComponentResourceManager(typeof(Bieu01));
            this.den = new System.Windows.Forms.DateTimePicker();
            this.tu = new System.Windows.Forms.DateTimePicker();
            this.label3 = new System.Windows.Forms.Label();
            this.label2 = new System.Windows.Forms.Label();
            this.ky = new System.Windows.Forms.ComboBox();
            this.nam = new System.Windows.Forms.NumericUpDown();
            this.loaibc = new System.Windows.Forms.ComboBox();
            this.label1 = new System.Windows.Forms.Label();
            this.label4 = new System.Windows.Forms.Label();
            this.btn_ok = new System.Windows.Forms.Button();
            this.btn_thoat = new System.Windows.Forms.Button();
            ((System.ComponentModel.ISupportInitialize)(this.nam)).BeginInit();
            this.SuspendLayout();
            // 
            // den
            // 
            this.den.CustomFormat = "dd/MM/yyyy";
            this.den.Font = new System.Drawing.Font("Tahoma", 9F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((byte)(0)));
            this.den.Format = System.Windows.Forms.DateTimePickerFormat.Custom;
            this.den.Location = new System.Drawing.Point(65, 48);
            this.den.Name = "den";
            this.den.Size = new System.Drawing.Size(96, 22);
            this.den.TabIndex = 21;
            // 
            // tu
            // 
            this.tu.CustomFormat = "dd/MM/yyyy";
            this.tu.Font = new System.Drawing.Font("Tahoma", 9F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((byte)(0)));
            this.tu.Format = System.Windows.Forms.DateTimePickerFormat.Custom;
            this.tu.Location = new System.Drawing.Point(220, 48);
            this.tu.Name = "tu";
            this.tu.Size = new System.Drawing.Size(96, 22);
            this.tu.TabIndex = 19;
            this.tu.Visible = false;
            // 
            // label3
            // 
            this.label3.ForeColor = System.Drawing.Color.Navy;
            this.label3.Location = new System.Drawing.Point(0, 48);
            this.label3.Name = "label3";
            this.label3.Size = new System.Drawing.Size(60, 23);
            this.label3.TabIndex = 20;
            this.label3.Text = "Đến ngày :";
            this.label3.TextAlign = System.Drawing.ContentAlignment.MiddleRight;
            // 
            // label2
            // 
            this.label2.ForeColor = System.Drawing.Color.Navy;
            this.label2.Location = new System.Drawing.Point(167, 48);
            this.label2.Name = "label2";
            this.label2.Size = new System.Drawing.Size(55, 23);
            this.label2.TabIndex = 18;
            this.label2.Text = "Từ ngày :";
            this.label2.TextAlign = System.Drawing.ContentAlignment.MiddleRight;
            this.label2.Visible = false;
            // 
            // ky
            // 
            this.ky.BackColor = System.Drawing.SystemColors.HighlightText;
            this.ky.DropDownStyle = System.Windows.Forms.ComboBoxStyle.DropDownList;
            this.ky.Font = new System.Drawing.Font("Tahoma", 9F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((byte)(0)));
            this.ky.Items.AddRange(new object[] {
            "03 tháng",
            "06 tháng",
            "09 tháng",
            "12 tháng"});
            this.ky.Location = new System.Drawing.Point(125, 20);
            this.ky.Name = "ky";
            this.ky.Size = new System.Drawing.Size(90, 22);
            this.ky.TabIndex = 16;
            // 
            // nam
            // 
            this.nam.BackColor = System.Drawing.SystemColors.HighlightText;
            this.nam.Font = new System.Drawing.Font("Tahoma", 9F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((byte)(0)));
            this.nam.Location = new System.Drawing.Point(264, 20);
            this.nam.Maximum = new decimal(new int[] {
            3000,
            0,
            0,
            0});
            this.nam.Minimum = new decimal(new int[] {
            1997,
            0,
            0,
            0});
            this.nam.Name = "nam";
            this.nam.Size = new System.Drawing.Size(52, 22);
            this.nam.TabIndex = 17;
            this.nam.TextAlign = System.Windows.Forms.HorizontalAlignment.Center;
            this.nam.Value = new decimal(new int[] {
            2006,
            0,
            0,
            0});
            // 
            // loaibc
            // 
            this.loaibc.DropDownStyle = System.Windows.Forms.ComboBoxStyle.DropDownList;
            this.loaibc.Font = new System.Drawing.Font("Tahoma", 9F, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point, ((byte)(0)));
            this.loaibc.Items.AddRange(new object[] {
            "Kỳ",
            "Ngày"});
            this.loaibc.Location = new System.Drawing.Point(65, 20);
            this.loaibc.Name = "loaibc";
            this.loaibc.Size = new System.Drawing.Size(60, 22);
            this.loaibc.TabIndex = 15;
            this.loaibc.SelectedIndexChanged += new System.EventHandler(this.loaibc_SelectedIndexChanged);
            // 
            // label1
            // 
            this.label1.ForeColor = System.Drawing.Color.Navy;
            this.label1.Location = new System.Drawing.Point(5, 20);
            this.label1.Name = "label1";
            this.label1.Size = new System.Drawing.Size(55, 23);
            this.label1.TabIndex = 14;
            this.label1.Text = "Theo :";
            this.label1.TextAlign = System.Drawing.ContentAlignment.MiddleRight;
            // 
            // label4
            // 
            this.label4.ForeColor = System.Drawing.Color.Navy;
            this.label4.Location = new System.Drawing.Point(215, 20);
            this.label4.Name = "label4";
            this.label4.Size = new System.Drawing.Size(50, 23);
            this.label4.TabIndex = 22;
            this.label4.Text = "năm :";
            this.label4.TextAlign = System.Drawing.ContentAlignment.MiddleRight;
            // 
            // btn_ok
            // 
            this.btn_ok.Font = new System.Drawing.Font("Microsoft Sans Serif", 8.25F, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, ((byte)(0)));
            this.btn_ok.ForeColor = System.Drawing.Color.DarkBlue;
            this.btn_ok.Image = global::Human.Properties.Resources.ok;
            this.btn_ok.ImageAlign = System.Drawing.ContentAlignment.MiddleLeft;
            this.btn_ok.Location = new System.Drawing.Point(84, 84);
            this.btn_ok.Name = "btn_ok";
            this.btn_ok.Size = new System.Drawing.Size(75, 28);
            this.btn_ok.TabIndex = 35;
            this.btn_ok.Text = "   &Chuyển";
            this.btn_ok.UseVisualStyleBackColor = true;
            this.btn_ok.Click += new System.EventHandler(this.btn_ok_Click);
            // 
            // btn_thoat
            // 
            this.btn_thoat.Font = new System.Drawing.Font("Microsoft Sans Serif", 8.25F, System.Drawing.FontStyle.Bold, System.Drawing.GraphicsUnit.Point, ((byte)(0)));
            this.btn_thoat.ForeColor = System.Drawing.Color.DarkBlue;
            this.btn_thoat.Image = global::Human.Properties.Resources.close_r;
            this.btn_thoat.ImageAlign = System.Drawing.ContentAlignment.MiddleLeft;
            this.btn_thoat.Location = new System.Drawing.Point(163, 84);
            this.btn_thoat.Name = "btn_thoat";
            this.btn_thoat.Size = new System.Drawing.Size(75, 28);
            this.btn_thoat.TabIndex = 35;
            this.btn_thoat.Text = "&Kết thúc";
            this.btn_thoat.TextAlign = System.Drawing.ContentAlignment.MiddleRight;
            this.btn_thoat.UseVisualStyleBackColor = true;
            this.btn_thoat.Click += new System.EventHandler(this.btn_thoat_Click);
            // 
            // Bieu01
            // 
            this.AutoScaleBaseSize = new System.Drawing.Size(5, 13);
            this.ClientSize = new System.Drawing.Size(322, 124);
            this.Controls.Add(this.btn_thoat);
            this.Controls.Add(this.btn_ok);
            this.Controls.Add(this.label4);
            this.Controls.Add(this.den);
            this.Controls.Add(this.tu);
            this.Controls.Add(this.label3);
            this.Controls.Add(this.label2);
            this.Controls.Add(this.ky);
            this.Controls.Add(this.nam);
            this.Controls.Add(this.loaibc);
            this.Controls.Add(this.label1);
            this.Icon = ((System.Drawing.Icon)(resources.GetObject("$this.Icon")));
            this.KeyPreview = true;
            this.Name = "Bieu01";
            this.StartPosition = System.Windows.Forms.FormStartPosition.CenterScreen;
            this.Text = "Chuyển Biểu 01 sang Medisoft2003";
            this.Load += new System.EventHandler(this.Biue01_Load);
            ((System.ComponentModel.ISupportInitialize)(this.nam)).EndInit();
            this.ResumeLayout(false);

		}
		#endregion

		[STAThread]
		
		private void Biue01_Load(object sender, System.EventArgs e)
		{
            user = m.user;
            loaibc.SelectedIndex=0;
			ky.SelectedIndex=0;
            nam.Value = Convert.ToDecimal(DateTime.Now.Year.ToString());
		}
		

		private void loaibc_SelectedIndexChanged(object sender, System.EventArgs e)
		{
			ky.Enabled=loaibc.SelectedIndex==0;
			nam.Enabled=ky.Enabled;
			tu.Enabled=!ky.Enabled;
			den.Enabled=tu.Enabled;
			if (loaibc.SelectedIndex==1) ky.SelectedIndex=-1;
			else if (ky.SelectedIndex==-1) ky.SelectedIndex=0;
		}
		
		private void Thuchien()
		{
			DataSet ds =new DataSet();
			//int i=1;
            int i = int.Parse(m.get_capid(10).ToString());	
            string sql = " ";

            sql = "select * from medibv.bieu_01 where to_date(to_char(ngay,'dd/mm/yyyy'),'dd/mm/yyyy') = to_date('" + s_den + "','dd/mm/yyyy')";
            ds = m.get_data(sql);
            if (ds.Tables[0].Rows.Count > 0)
            {
                try
                {
                    m.Execute_data("delete from medibv.bieu_01 where to_date(to_char(ngay,'dd/mm/yyyy'),'dd/mm/yyyy') = to_date('" + s_den + "','dd/mm/yyyy')");

                }
                catch { }
            }
            //			tổng số CBCCVC	
			sql =truyvan_tong();
            sql += " group by to_date('" + s_den + "','dd/mm/yyyy')";
			ds =m.get_data(sql);
            if (ds.Tables[0].Rows.Count > 0)
            {
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    int c1 = 0, c2 = 0, c3 = 0, c4 = 0, c5 = 0, c6 = 0, c7 = 0, c8 = 0, c9 = 0, c10 = 0, c11 = 0, c12 = 0, c13 = 0, c14 = 0, c15 = 0, c16 = 0, c17 = 0, c18 = 0, c19 = 0, c20 = 0, c21 = 0, c22 = 0, c23 = 0;
                    c1 = dr["tongso"].ToString().Trim() != "" ? int.Parse(dr["tongso"].ToString()) : 0;
                    c2 = dr["tongnu"].ToString().Trim() != "" ? int.Parse(dr["tongnu"].ToString()) : 0;
                    c3 = dr["gs"].ToString().Trim() != "" ? int.Parse(dr["gs"].ToString()) : 0;
                    c4 = dr["ts"].ToString().Trim() != "" ? int.Parse(dr["ts"].ToString()) : 0;
                    c5 = dr["ths"].ToString().Trim() != "" ? int.Parse(dr["ths"].ToString()) : 0;
                    c6 = dr["CKII"].ToString().Trim() != "" ? int.Parse(dr["CKII"].ToString()) : 0;
                    c7 = dr["CKI"].ToString().Trim() != "" ? int.Parse(dr["CKI"].ToString()) : 0;
                    c8 = dr["BS"].ToString().Trim() != "" ? int.Parse(dr["bs"].ToString()) : 0;
                    c9 = dr["dsdh"].ToString().Trim() != "" ? int.Parse(dr["dsdh"].ToString()) : 0;
                    c10 = dr["dhkhac"].ToString().Trim() != "" ? int.Parse(dr["dhkhac"].ToString()) : 0;
                    c11 = dr["ytdh"].ToString().Trim() != "" ? int.Parse(dr["ytdh"].ToString()) : 0;
                    c12 = dr["ysth"].ToString().Trim() != "" ? int.Parse(dr["ysth"].ToString()) : 0;
                    c13 = dr["ktv"].ToString().Trim() != "" ? int.Parse(dr["ktv"].ToString()) : 0;
                    c14 = dr["dsth"].ToString().Trim() != "" ? int.Parse(dr["dsth"].ToString()) : 0;
                    c15 = dr["ytth"].ToString().Trim() != "" ? int.Parse(dr["ytth"].ToString()) : 0;
                    c16 = dr["nhsth"].ToString().Trim() != "" ? int.Parse(dr["nhsth"].ToString()) : 0;
                    c17 = dr["thkhac"].ToString().Trim() != "" ? int.Parse(dr["thkhac"].ToString()) : 0;
                    c18 = dr["ytsh"].ToString().Trim() != "" ? int.Parse(dr["ytsh"].ToString()) : 0;
                    c19 = dr["nhssh"].ToString().Trim() != "" ? int.Parse(dr["nhssh"].ToString()) : 0;
                    c20 = dr["dta"].ToString().Trim() != "" ? int.Parse(dr["dta"].ToString()) : 0;
                    //c21 = dr["cbkhac"].ToString().Trim() != "" ? int.Parse(dr["cbkhac"].ToString()) : 0;
                    c22 = dr["tquy"].ToString().Trim() != "" ? int.Parse(dr["Tquy"].ToString()) : 0;
                    c23 = dr["Nquy"].ToString().Trim() != "" ? int.Parse(dr["Nquy"].ToString()) : 0;
                    c21=c1-(c3+c4+c5+c6+c7+c8+c9+c10+c11+c12+c13+c14+c15+c16+c17+c18+c19+c20);
                    dr["cbkhac"] = c21.ToString();
                }
            }
			Capnhat(ds,i,1);
			//			tổng số nữ				
			sql =truyvan_tong();	
			sql+=" and to_char(l.phai) <> 'Nam'";
            sql += " group by to_date('" + s_den.Trim() + "','dd/mm/yyyy')";
			ds =m.get_data(sql);
            if (ds.Tables[0].Rows.Count > 0)
            {
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    int c1 = 0, c2 = 0, c3 = 0, c4 = 0, c5 = 0, c6 = 0, c7 = 0, c8 = 0, c9 = 0, c10 = 0, c11 = 0, c12 = 0, c13 = 0, c14 = 0, c15 = 0, c16 = 0, c17 = 0, c18 = 0, c19 = 0, c20 = 0, c21 = 0, c22 = 0, c23 = 0;
                    c1 = dr["tongso"].ToString().Trim() != "" ? int.Parse(dr["tongso"].ToString()) : 0;
                    c2 = dr["tongnu"].ToString().Trim() != "" ? int.Parse(dr["tongnu"].ToString()) : 0;
                    c3 = dr["gs"].ToString().Trim() != "" ? int.Parse(dr["gs"].ToString()) : 0;
                    c4 = dr["ts"].ToString().Trim() != "" ? int.Parse(dr["ts"].ToString()) : 0;
                    c5 = dr["ths"].ToString().Trim() != "" ? int.Parse(dr["ths"].ToString()) : 0;
                    c6 = dr["CKII"].ToString().Trim() != "" ? int.Parse(dr["CKII"].ToString()) : 0;
                    c7 = dr["CKI"].ToString().Trim() != "" ? int.Parse(dr["CKI"].ToString()) : 0;
                    c8 = dr["BS"].ToString().Trim() != "" ? int.Parse(dr["bs"].ToString()) : 0;
                    c9 = dr["dsdh"].ToString().Trim() != "" ? int.Parse(dr["dsdh"].ToString()) : 0;
                    c10 = dr["dhkhac"].ToString().Trim() != "" ? int.Parse(dr["dhkhac"].ToString()) : 0;
                    c11 = dr["ytdh"].ToString().Trim() != "" ? int.Parse(dr["ytdh"].ToString()) : 0;
                    c12 = dr["ysth"].ToString().Trim() != "" ? int.Parse(dr["ysth"].ToString()) : 0;
                    c13 = dr["ktv"].ToString().Trim() != "" ? int.Parse(dr["ktv"].ToString()) : 0;
                    c14 = dr["dsth"].ToString().Trim() != "" ? int.Parse(dr["dsth"].ToString()) : 0;
                    c15 = dr["ytth"].ToString().Trim() != "" ? int.Parse(dr["ytth"].ToString()) : 0;
                    c16 = dr["nhsth"].ToString().Trim() != "" ? int.Parse(dr["nhsth"].ToString()) : 0;
                    c17 = dr["thkhac"].ToString().Trim() != "" ? int.Parse(dr["thkhac"].ToString()) : 0;
                    c18 = dr["ytsh"].ToString().Trim() != "" ? int.Parse(dr["ytsh"].ToString()) : 0;
                    c19 = dr["nhssh"].ToString().Trim() != "" ? int.Parse(dr["nhssh"].ToString()) : 0;
                    c20 = dr["dta"].ToString().Trim() != "" ? int.Parse(dr["dta"].ToString()) : 0;
                    //c21 = dr["cbkhac"].ToString().Trim() != "" ? int.Parse(dr["cbkhac"].ToString()) : 0;
                    c22 = dr["tquy"].ToString().Trim() != "" ? int.Parse(dr["Tquy"].ToString()) : 0;
                    c23 = dr["Nquy"].ToString().Trim() != "" ? int.Parse(dr["Nquy"].ToString()) : 0;
                    c21 = c1 - (c3 + c4 + c5 + c6 + c7 + c8 + c9 + c10 + c11 + c12 + c13 + c14 + c15 + c16 + c17 + c18 + c19 + c20);
                    dr["cbkhac"] = c21.ToString();
                }
            }
			Capnhat(ds,i,2);
			//			Tổng các phòng			
			sql =truyvan_tong();	
			sql+=" and substr(trim(bp.mabsoft),1,1)='P'";
            sql += " group by to_date('" + s_den.Trim() + "','dd/mm/yyyy'),substr(trim(bp.mabsoft),1,1)";
			ds =m.get_data(sql);
            if (ds.Tables[0].Rows.Count > 0)
            {
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    int c1 = 0, c2 = 0, c3 = 0, c4 = 0, c5 = 0, c6 = 0, c7 = 0, c8 = 0, c9 = 0, c10 = 0, c11 = 0, c12 = 0, c13 = 0, c14 = 0, c15 = 0, c16 = 0, c17 = 0, c18 = 0, c19 = 0, c20 = 0, c21 = 0, c22 = 0, c23 = 0;
                    c1 = dr["tongso"].ToString().Trim() != "" ? int.Parse(dr["tongso"].ToString()) : 0;
                    c2 = dr["tongnu"].ToString().Trim() != "" ? int.Parse(dr["tongnu"].ToString()) : 0;
                    c3 = dr["gs"].ToString().Trim() != "" ? int.Parse(dr["gs"].ToString()) : 0;
                    c4 = dr["ts"].ToString().Trim() != "" ? int.Parse(dr["ts"].ToString()) : 0;
                    c5 = dr["ths"].ToString().Trim() != "" ? int.Parse(dr["ths"].ToString()) : 0;
                    c6 = dr["CKII"].ToString().Trim() != "" ? int.Parse(dr["CKII"].ToString()) : 0;
                    c7 = dr["CKI"].ToString().Trim() != "" ? int.Parse(dr["CKI"].ToString()) : 0;
                    c8 = dr["BS"].ToString().Trim() != "" ? int.Parse(dr["bs"].ToString()) : 0;
                    c9 = dr["dsdh"].ToString().Trim() != "" ? int.Parse(dr["dsdh"].ToString()) : 0;
                    c10 = dr["dhkhac"].ToString().Trim() != "" ? int.Parse(dr["dhkhac"].ToString()) : 0;
                    c11 = dr["ytdh"].ToString().Trim() != "" ? int.Parse(dr["ytdh"].ToString()) : 0;
                    c12 = dr["ysth"].ToString().Trim() != "" ? int.Parse(dr["ysth"].ToString()) : 0;
                    c13 = dr["ktv"].ToString().Trim() != "" ? int.Parse(dr["ktv"].ToString()) : 0;
                    c14 = dr["dsth"].ToString().Trim() != "" ? int.Parse(dr["dsth"].ToString()) : 0;
                    c15 = dr["ytth"].ToString().Trim() != "" ? int.Parse(dr["ytth"].ToString()) : 0;
                    c16 = dr["nhsth"].ToString().Trim() != "" ? int.Parse(dr["nhsth"].ToString()) : 0;
                    c17 = dr["thkhac"].ToString().Trim() != "" ? int.Parse(dr["thkhac"].ToString()) : 0;
                    c18 = dr["ytsh"].ToString().Trim() != "" ? int.Parse(dr["ytsh"].ToString()) : 0;
                    c19 = dr["nhssh"].ToString().Trim() != "" ? int.Parse(dr["nhssh"].ToString()) : 0;
                    c20 = dr["dta"].ToString().Trim() != "" ? int.Parse(dr["dta"].ToString()) : 0;
                    //c21 = dr["cbkhac"].ToString().Trim() != "" ? int.Parse(dr["cbkhac"].ToString()) : 0;
                    c22 = dr["tquy"].ToString().Trim() != "" ? int.Parse(dr["Tquy"].ToString()) : 0;
                    c23 = dr["Nquy"].ToString().Trim() != "" ? int.Parse(dr["Nquy"].ToString()) : 0;
                    c21 = c1 - (c3 + c4 + c5 + c6 + c7 + c8 + c9 + c10 + c11 + c12 + c13 + c14 + c15 + c16 + c17 + c18 + c19 + c20);
                    dr["cbkhac"] = c21.ToString();
                }
            }
			Capnhat(ds,i,3);
			//			Các phòng
			sql =truyvan();	
			sql+=" and substr(trim(bp.mabsoft),1,1)='P'";
            sql += " group by bp.mabaocao,to_date('" + s_den.Trim() + "','dd/mm/yyyy')";
			ds =m.get_data(sql);
            if (ds.Tables[0].Rows.Count > 0)
            {
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    int c1 = 0, c2 = 0, c3 = 0, c4 = 0, c5 = 0, c6 = 0, c7 = 0, c8 = 0, c9 = 0, c10 = 0, c11 = 0, c12 = 0, c13 = 0, c14 = 0, c15 = 0, c16 = 0, c17 = 0, c18 = 0, c19 = 0, c20 = 0, c21 = 0, c22 = 0, c23 = 0;
                    c1 = dr["tongso"].ToString().Trim() != "" ? int.Parse(dr["tongso"].ToString()) : 0;
                    c2 = dr["tongnu"].ToString().Trim() != "" ? int.Parse(dr["tongnu"].ToString()) : 0;
                    c3 = dr["gs"].ToString().Trim() != "" ? int.Parse(dr["gs"].ToString()) : 0;
                    c4 = dr["ts"].ToString().Trim() != "" ? int.Parse(dr["ts"].ToString()) : 0;
                    c5 = dr["ths"].ToString().Trim() != "" ? int.Parse(dr["ths"].ToString()) : 0;
                    c6 = dr["CKII"].ToString().Trim() != "" ? int.Parse(dr["CKII"].ToString()) : 0;
                    c7 = dr["CKI"].ToString().Trim() != "" ? int.Parse(dr["CKI"].ToString()) : 0;
                    c8 = dr["BS"].ToString().Trim() != "" ? int.Parse(dr["bs"].ToString()) : 0;
                    c9 = dr["dsdh"].ToString().Trim() != "" ? int.Parse(dr["dsdh"].ToString()) : 0;
                    c10 = dr["dhkhac"].ToString().Trim() != "" ? int.Parse(dr["dhkhac"].ToString()) : 0;
                    c11 = dr["ytdh"].ToString().Trim() != "" ? int.Parse(dr["ytdh"].ToString()) : 0;
                    c12 = dr["ysth"].ToString().Trim() != "" ? int.Parse(dr["ysth"].ToString()) : 0;
                    c13 = dr["ktv"].ToString().Trim() != "" ? int.Parse(dr["ktv"].ToString()) : 0;
                    c14 = dr["dsth"].ToString().Trim() != "" ? int.Parse(dr["dsth"].ToString()) : 0;
                    c15 = dr["ytth"].ToString().Trim() != "" ? int.Parse(dr["ytth"].ToString()) : 0;
                    c16 = dr["nhsth"].ToString().Trim() != "" ? int.Parse(dr["nhsth"].ToString()) : 0;
                    c17 = dr["thkhac"].ToString().Trim() != "" ? int.Parse(dr["thkhac"].ToString()) : 0;
                    c18 = dr["ytsh"].ToString().Trim() != "" ? int.Parse(dr["ytsh"].ToString()) : 0;
                    c19 = dr["nhssh"].ToString().Trim() != "" ? int.Parse(dr["nhssh"].ToString()) : 0;
                    c20 = dr["dta"].ToString().Trim() != "" ? int.Parse(dr["dta"].ToString()) : 0;
                    //c21 = dr["cbkhac"].ToString().Trim() != "" ? int.Parse(dr["cbkhac"].ToString()) : 0;
                    c22 = dr["tquy"].ToString().Trim() != "" ? int.Parse(dr["Tquy"].ToString()) : 0;
                    c23 = dr["Nquy"].ToString().Trim() != "" ? int.Parse(dr["Nquy"].ToString()) : 0;
                    c21 = c1 - (c3 + c4 + c5 + c6 + c7 + c8 + c9 + c10 + c11 + c12 + c13 + c14 + c15 + c16 + c17 + c18 + c19 + c20);
                    dr["cbkhac"] = c21.ToString();
                }
            }
			Capnhat(ds,i,0);
			//			Tổng lâm sàng
			//i =4 + ds.Tables[0].Rows.Count;
			sql =truyvan_tong();	
			sql+=" and substr(trim(bp.mabsoft),1,1)='L'";
            sql += " group by to_date('" + s_den.Trim() + "','dd/mm/yyyy'),substr(trim(bp.mabsoft),1,1)";
			ds =m.get_data(sql);
            if (ds.Tables[0].Rows.Count > 0)
            {
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    int c1 = 0, c2 = 0, c3 = 0, c4 = 0, c5 = 0, c6 = 0, c7 = 0, c8 = 0, c9 = 0, c10 = 0, c11 = 0, c12 = 0, c13 = 0, c14 = 0, c15 = 0, c16 = 0, c17 = 0, c18 = 0, c19 = 0, c20 = 0, c21 = 0, c22 = 0, c23 = 0;
                    c1 = dr["tongso"].ToString().Trim() != "" ? int.Parse(dr["tongso"].ToString()) : 0;
                    c2 = dr["tongnu"].ToString().Trim() != "" ? int.Parse(dr["tongnu"].ToString()) : 0;
                    c3 = dr["gs"].ToString().Trim() != "" ? int.Parse(dr["gs"].ToString()) : 0;
                    c4 = dr["ts"].ToString().Trim() != "" ? int.Parse(dr["ts"].ToString()) : 0;
                    c5 = dr["ths"].ToString().Trim() != "" ? int.Parse(dr["ths"].ToString()) : 0;
                    c6 = dr["CKII"].ToString().Trim() != "" ? int.Parse(dr["CKII"].ToString()) : 0;
                    c7 = dr["CKI"].ToString().Trim() != "" ? int.Parse(dr["CKI"].ToString()) : 0;
                    c8 = dr["BS"].ToString().Trim() != "" ? int.Parse(dr["bs"].ToString()) : 0;
                    c9 = dr["dsdh"].ToString().Trim() != "" ? int.Parse(dr["dsdh"].ToString()) : 0;
                    c10 = dr["dhkhac"].ToString().Trim() != "" ? int.Parse(dr["dhkhac"].ToString()) : 0;
                    c11 = dr["ytdh"].ToString().Trim() != "" ? int.Parse(dr["ytdh"].ToString()) : 0;
                    c12 = dr["ysth"].ToString().Trim() != "" ? int.Parse(dr["ysth"].ToString()) : 0;
                    c13 = dr["ktv"].ToString().Trim() != "" ? int.Parse(dr["ktv"].ToString()) : 0;
                    c14 = dr["dsth"].ToString().Trim() != "" ? int.Parse(dr["dsth"].ToString()) : 0;
                    c15 = dr["ytth"].ToString().Trim() != "" ? int.Parse(dr["ytth"].ToString()) : 0;
                    c16 = dr["nhsth"].ToString().Trim() != "" ? int.Parse(dr["nhsth"].ToString()) : 0;
                    c17 = dr["thkhac"].ToString().Trim() != "" ? int.Parse(dr["thkhac"].ToString()) : 0;
                    c18 = dr["ytsh"].ToString().Trim() != "" ? int.Parse(dr["ytsh"].ToString()) : 0;
                    c19 = dr["nhssh"].ToString().Trim() != "" ? int.Parse(dr["nhssh"].ToString()) : 0;
                    c20 = dr["dta"].ToString().Trim() != "" ? int.Parse(dr["dta"].ToString()) : 0;
                    //c21 = dr["cbkhac"].ToString().Trim() != "" ? int.Parse(dr["cbkhac"].ToString()) : 0;
                    c22 = dr["tquy"].ToString().Trim() != "" ? int.Parse(dr["Tquy"].ToString()) : 0;
                    c23 = dr["Nquy"].ToString().Trim() != "" ? int.Parse(dr["Nquy"].ToString()) : 0;
                    c21 = c1 - (c3 + c4 + c5 + c6 + c7 + c8 + c9 + c10 + c11 + c12 + c13 + c14 + c15 + c16 + c17 + c18 + c19 + c20);
                    dr["cbkhac"] = c21.ToString();
                }
            }
			Capnhat(ds,i,12);
			//			Khoa lâm sàng
			//i+=1;
			sql =truyvan();	
			sql+=" and substr(trim(bp.mabsoft),1,1)='L'";
            sql += " group by bp.mabaocao,to_date('" + s_den.Trim() + "','dd/mm/yyyy')";
			ds =m.get_data(sql);
            if (ds.Tables[0].Rows.Count > 0)
            {
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    int c1 = 0, c2 = 0, c3 = 0, c4 = 0, c5 = 0, c6 = 0, c7 = 0, c8 = 0, c9 = 0, c10 = 0, c11 = 0, c12 = 0, c13 = 0, c14 = 0, c15 = 0, c16 = 0, c17 = 0, c18 = 0, c19 = 0, c20 = 0, c21 = 0, c22 = 0, c23 = 0;
                    c1 = dr["tongso"].ToString().Trim() != "" ? int.Parse(dr["tongso"].ToString()) : 0;
                    c2 = dr["tongnu"].ToString().Trim() != "" ? int.Parse(dr["tongnu"].ToString()) : 0;
                    c3 = dr["gs"].ToString().Trim() != "" ? int.Parse(dr["gs"].ToString()) : 0;
                    c4 = dr["ts"].ToString().Trim() != "" ? int.Parse(dr["ts"].ToString()) : 0;
                    c5 = dr["ths"].ToString().Trim() != "" ? int.Parse(dr["ths"].ToString()) : 0;
                    c6 = dr["CKII"].ToString().Trim() != "" ? int.Parse(dr["CKII"].ToString()) : 0;
                    c7 = dr["CKI"].ToString().Trim() != "" ? int.Parse(dr["CKI"].ToString()) : 0;
                    c8 = dr["BS"].ToString().Trim() != "" ? int.Parse(dr["bs"].ToString()) : 0;
                    c9 = dr["dsdh"].ToString().Trim() != "" ? int.Parse(dr["dsdh"].ToString()) : 0;
                    c10 = dr["dhkhac"].ToString().Trim() != "" ? int.Parse(dr["dhkhac"].ToString()) : 0;
                    c11 = dr["ytdh"].ToString().Trim() != "" ? int.Parse(dr["ytdh"].ToString()) : 0;
                    c12 = dr["ysth"].ToString().Trim() != "" ? int.Parse(dr["ysth"].ToString()) : 0;
                    c13 = dr["ktv"].ToString().Trim() != "" ? int.Parse(dr["ktv"].ToString()) : 0;
                    c14 = dr["dsth"].ToString().Trim() != "" ? int.Parse(dr["dsth"].ToString()) : 0;
                    c15 = dr["ytth"].ToString().Trim() != "" ? int.Parse(dr["ytth"].ToString()) : 0;
                    c16 = dr["nhsth"].ToString().Trim() != "" ? int.Parse(dr["nhsth"].ToString()) : 0;
                    c17 = dr["thkhac"].ToString().Trim() != "" ? int.Parse(dr["thkhac"].ToString()) : 0;
                    c18 = dr["ytsh"].ToString().Trim() != "" ? int.Parse(dr["ytsh"].ToString()) : 0;
                    c19 = dr["nhssh"].ToString().Trim() != "" ? int.Parse(dr["nhssh"].ToString()) : 0;
                    c20 = dr["dta"].ToString().Trim() != "" ? int.Parse(dr["dta"].ToString()) : 0;
                    //c21 = dr["cbkhac"].ToString().Trim() != "" ? int.Parse(dr["cbkhac"].ToString()) : 0;
                    c22 = dr["tquy"].ToString().Trim() != "" ? int.Parse(dr["Tquy"].ToString()) : 0;
                    c23 = dr["Nquy"].ToString().Trim() != "" ? int.Parse(dr["Nquy"].ToString()) : 0;
                    c21 = c1 - (c3 + c4 + c5 + c6 + c7 + c8 + c9 + c10 + c11 + c12 + c13 + c14 + c15 + c16 + c17 + c18 + c19 + c20);
                    dr["cbkhac"] = c21.ToString();
                }
            }
			Capnhat(ds,i,0);			
			//			Tổng cận lâm sàng			
			//i +=ds.Tables[0].Rows.Count +1;
			sql =truyvan_tong();	
			sql+=" and substr(trim(bp.mabsoft),1,1)='C'";
			sql+=" group by to_date('"+s_den.Trim()+"','dd/mm/yyyy'),substr(trim(bp.mabsoft),1,1)";
			ds =m.get_data(sql);
            if (ds.Tables[0].Rows.Count > 0)
            {
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    int c1 = 0, c2 = 0, c3 = 0, c4 = 0, c5 = 0, c6 = 0, c7 = 0, c8 = 0, c9 = 0, c10 = 0, c11 = 0, c12 = 0, c13 = 0, c14 = 0, c15 = 0, c16 = 0, c17 = 0, c18 = 0, c19 = 0, c20 = 0, c21 = 0, c22 = 0, c23 = 0;
                    c1 = dr["tongso"].ToString().Trim() != "" ? int.Parse(dr["tongso"].ToString()) : 0;
                    c2 = dr["tongnu"].ToString().Trim() != "" ? int.Parse(dr["tongnu"].ToString()) : 0;
                    c3 = dr["gs"].ToString().Trim() != "" ? int.Parse(dr["gs"].ToString()) : 0;
                    c4 = dr["ts"].ToString().Trim() != "" ? int.Parse(dr["ts"].ToString()) : 0;
                    c5 = dr["ths"].ToString().Trim() != "" ? int.Parse(dr["ths"].ToString()) : 0;
                    c6 = dr["CKII"].ToString().Trim() != "" ? int.Parse(dr["CKII"].ToString()) : 0;
                    c7 = dr["CKI"].ToString().Trim() != "" ? int.Parse(dr["CKI"].ToString()) : 0;
                    c8 = dr["BS"].ToString().Trim() != "" ? int.Parse(dr["bs"].ToString()) : 0;
                    c9 = dr["dsdh"].ToString().Trim() != "" ? int.Parse(dr["dsdh"].ToString()) : 0;
                    c10 = dr["dhkhac"].ToString().Trim() != "" ? int.Parse(dr["dhkhac"].ToString()) : 0;
                    c11 = dr["ytdh"].ToString().Trim() != "" ? int.Parse(dr["ytdh"].ToString()) : 0;
                    c12 = dr["ysth"].ToString().Trim() != "" ? int.Parse(dr["ysth"].ToString()) : 0;
                    c13 = dr["ktv"].ToString().Trim() != "" ? int.Parse(dr["ktv"].ToString()) : 0;
                    c14 = dr["dsth"].ToString().Trim() != "" ? int.Parse(dr["dsth"].ToString()) : 0;
                    c15 = dr["ytth"].ToString().Trim() != "" ? int.Parse(dr["ytth"].ToString()) : 0;
                    c16 = dr["nhsth"].ToString().Trim() != "" ? int.Parse(dr["nhsth"].ToString()) : 0;
                    c17 = dr["thkhac"].ToString().Trim() != "" ? int.Parse(dr["thkhac"].ToString()) : 0;
                    c18 = dr["ytsh"].ToString().Trim() != "" ? int.Parse(dr["ytsh"].ToString()) : 0;
                    c19 = dr["nhssh"].ToString().Trim() != "" ? int.Parse(dr["nhssh"].ToString()) : 0;
                    c20 = dr["dta"].ToString().Trim() != "" ? int.Parse(dr["dta"].ToString()) : 0;
                    //c21 = dr["cbkhac"].ToString().Trim() != "" ? int.Parse(dr["cbkhac"].ToString()) : 0;
                    c22 = dr["tquy"].ToString().Trim() != "" ? int.Parse(dr["Tquy"].ToString()) : 0;
                    c23 = dr["Nquy"].ToString().Trim() != "" ? int.Parse(dr["Nquy"].ToString()) : 0;
                    c21 = c1 - (c3 + c4 + c5 + c6 + c7 + c8 + c9 + c10 + c11 + c12 + c13 + c14 + c15 + c16 + c17 + c18 + c19 + c20);
                    dr["cbkhac"] = c21.ToString();
                }
            }
			Capnhat(ds,i,41);
			//			Khoa cận lâm sàng
			//i += 1;
			sql =truyvan();	
			sql+=" and substr(trim(bp.mabsoft),1,1)='C'";
            sql += " group by bp.mabaocao,to_date('" + s_den.Trim() + "','dd/mm/yyyy')";
			ds =m.get_data(sql);
            if (ds.Tables[0].Rows.Count > 0)
            {
                foreach (DataRow dr in ds.Tables[0].Rows)
                {
                    int c1 = 0, c2 = 0, c3 = 0, c4 = 0, c5 = 0, c6 = 0, c7 = 0, c8 = 0, c9 = 0, c10 = 0, c11 = 0, c12 = 0, c13 = 0, c14 = 0, c15 = 0, c16 = 0, c17 = 0, c18 = 0, c19 = 0, c20 = 0, c21 = 0, c22 = 0, c23 = 0;
                    c1 = dr["tongso"].ToString().Trim() != "" ? int.Parse(dr["tongso"].ToString()) : 0;
                    c2 = dr["tongnu"].ToString().Trim() != "" ? int.Parse(dr["tongnu"].ToString()) : 0;
                    c3 = dr["gs"].ToString().Trim() != "" ? int.Parse(dr["gs"].ToString()) : 0;
                    c4 = dr["ts"].ToString().Trim() != "" ? int.Parse(dr["ts"].ToString()) : 0;
                    c5 = dr["ths"].ToString().Trim() != "" ? int.Parse(dr["ths"].ToString()) : 0;
                    c6 = dr["CKII"].ToString().Trim() != "" ? int.Parse(dr["CKII"].ToString()) : 0;
                    c7 = dr["CKI"].ToString().Trim() != "" ? int.Parse(dr["CKI"].ToString()) : 0;
                    c8 = dr["BS"].ToString().Trim() != "" ? int.Parse(dr["bs"].ToString()) : 0;
                    c9 = dr["dsdh"].ToString().Trim() != "" ? int.Parse(dr["dsdh"].ToString()) : 0;
                    c10 = dr["dhkhac"].ToString().Trim() != "" ? int.Parse(dr["dhkhac"].ToString()) : 0;
                    c11 = dr["ytdh"].ToString().Trim() != "" ? int.Parse(dr["ytdh"].ToString()) : 0;
                    c12 = dr["ysth"].ToString().Trim() != "" ? int.Parse(dr["ysth"].ToString()) : 0;
                    c13 = dr["ktv"].ToString().Trim() != "" ? int.Parse(dr["ktv"].ToString()) : 0;
                    c14 = dr["dsth"].ToString().Trim() != "" ? int.Parse(dr["dsth"].ToString()) : 0;
                    c15 = dr["ytth"].ToString().Trim() != "" ? int.Parse(dr["ytth"].ToString()) : 0;
                    c16 = dr["nhsth"].ToString().Trim() != "" ? int.Parse(dr["nhsth"].ToString()) : 0;
                    c17 = dr["thkhac"].ToString().Trim() != "" ? int.Parse(dr["thkhac"].ToString()) : 0;
                    c18 = dr["ytsh"].ToString().Trim() != "" ? int.Parse(dr["ytsh"].ToString()) : 0;
                    c19 = dr["nhssh"].ToString().Trim() != "" ? int.Parse(dr["nhssh"].ToString()) : 0;
                    c20 = dr["dta"].ToString().Trim() != "" ? int.Parse(dr["dta"].ToString()) : 0;
                    //c21 = dr["cbkhac"].ToString().Trim() != "" ? int.Parse(dr["cbkhac"].ToString()) : 0;
                    c22 = dr["tquy"].ToString().Trim() != "" ? int.Parse(dr["Tquy"].ToString()) : 0;
                    c23 = dr["Nquy"].ToString().Trim() != "" ? int.Parse(dr["Nquy"].ToString()) : 0;
                    c21 = c1 - (c3 + c4 + c5 + c6 + c7 + c8 + c9 + c10 + c11 + c12 + c13 + c14 + c15 + c16 + c17 + c18 + c19 + c20);
                    dr["cbkhac"] = c21.ToString();
                }
            }
			Capnhat(ds,i,0);
		}
        private string truyvan_tong()
        {
            string sql = "";
            sql = " SELECT '"+s_den.Trim()+"' as ngay,";
            sql += " count(l.manv) as tongso,";
            sql += " (sum(case when l.phai<>'Nam'  then 1 else 0 end)) as tongnu,";
            sql += " sum(case when l.mahocham in ('2','3')  then 1 else 0 end) as GS,";
            sql += " sum(case when l.td_nnghiep in ('01')  then 1 else 0 end) as TS,";
            sql += " sum(case when l.td_nnghiep in ('02')  then 1 else 0 end) as ThS,";
            sql += " sum(case when l.td_nnghiep ='08'  and (l.mahocham not in ('2','3') or l.mahocham ='')  then 1 else 0 end ) as CKII,";
            sql += " sum(case when l.td_nnghiep='07' and (l.mahocham not in ('2','3') or l.mahocham ='')  then 1 else 0 end) as CKI,";
            sql += " sum(case when dt.manhom in ('01') and bc.mabc in ('DH') then 1 else 0 end) as BS,";
            sql += " sum(case when dt.manhom in ('02') and bc.mabc in ('DH')  then 1 else 0 end) as DSDH,";
            sql += " sum(case when dt.manhom in ('06') and bc.mabc in ('DH')  then 1 else 0 end) as YTDH,";
            sql += " sum(case when dt.manhom not in ('01','02','06') and bc.mabc in ('DH')  then 1 else 0 end) as DHkhac,";
            //sql += " sum(case when bc.mabc in ('CD')  then 1 else 0 end) as CDang,";
            sql += " sum(case when dt.manhom in ('07') and bc.mabc in ('TH') then 1 else 0 end) as YSTH,";
            sql += " sum(case when dt.manhom in ('04') and bc.mabc in ('TH') then 1 else 0 end) as KTV,";
            sql += " sum(case when dt.manhom in ('02') and bc.mabc in ('TH') then 1 else 0 end) as DSTH,";
            sql += " sum(case when dt.manhom in ('06') and bc.mabc in ('TH') then 1 else 0 end) as YTTH,";
            sql += " sum(case when dt.manhom in ('08') and bc.mabc in ('TH') then 1 else 0 end) as NHSTH,";
            sql += " sum(case when dt.manhom not in ('02','04','06','07','08') and bc.mabc in ('TH') then 1 else 0 end) as THkhac,";
            sql += " sum(case when dt.manhom in ('06')  and bc.mabc in ('SH') then 1 else 0 end) as YTSH,";
            sql += " sum(case when dt.manhom in ('08') and bc.mabc in ('SH') then 1 else 0 end) as NHSSH,";
            sql += " sum(case when dt.manhom in ('03') and bc.mabc in ('SH') then 1 else 0 end) as Dta,";
            sql += " 0 as CBkhac,";
            sql += " sum(case when (mh.nhombc=2) then 1 else 0 end) as Tquy,";
            sql += " sum(case when (mh.nhombc =3) then 1 else 0 end) as Nquy";
            sql += " FROM " + user + ".llnv l inner join " + user + ".hopdong hd on l.manv=hd.manv";
            sql += " inner join "+user+".bophan bp on  bp.madv=l.madv";
            sql += " left join " + user + ".dmcd cd on cd.ma=l.macd";
            sql += " left join " + user + ".bangcap bc on bc.mabangcap=l.td_nnghiep";
            sql += " left join " + user + ".mangach mn on hd.mangach=mn.ma";
            sql += " left join " + user + ".mahd mh on hd.loaihd=mh.mahd";
            sql += " left join " + user + ".ctdaotao dt on l.nnghiep=dt.mactdt";
            sql += " inner join " + user + ".max_lan ml on hd.manv=ml.manv";
            sql += " WHERE  (hd.ngaynghi is null or to_date(to_char(hd.ngaynghi,'dd/mm/yyyy'),'dd/mm/yyyy') > to_date('" + s_den + "','dd/mm/yyyy')) ";
            sql += " and to_date(to_char(hd.ngaydenct,'dd/mm/yyyy'),'dd/mm/yyyy')<=to_date('" + s_den + "','dd/mm/yyyy') ";
            sql += " and hd.lan=ml.lan";
            sql += " and upper(substr(trim(bp.mabsoft),1,1)) in ('L','C','P','K')";         
            return sql;
        }
        private string truyvan()
        {            
            string sql = "";
            sql = " SELECT bp.mabaocao,'" + s_den.Trim() + "' as ngay,";
            sql += " count(l.manv) as tongso,";
            sql += " (sum(case when l.phai='Nữ'  then 1 else 0 end)) as tongnu,";
            sql += " sum(case when l.mahocham in ('2','3')  then 1 else 0 end) as GS,";
            sql += " sum(case when l.td_nnghiep in ('01')  then 1 else 0 end) as TS,";
            sql += " sum(case when l.td_nnghiep in ('02')  then 1 else 0 end) as ThS,";
            sql += " sum(case when l.td_nnghiep ='08'  and (l.mahocham not in ('2','3') or l.mahocham ='')  then 1 else 0 end ) as CKII,";
            sql += " sum(case when l.td_nnghiep='07' and (l.mahocham not in ('2','3') or l.mahocham ='')  then 1 else 0 end) as CKI,";
            sql += " sum(case when dt.manhom in ('01') and bc.mabc in ('DH') then 1 else 0 end) as BS,";
            sql += " sum(case when dt.manhom in ('02') and bc.mabc in ('DH')  then 1 else 0 end) as DSDH,";
            sql += " sum(case when dt.manhom in ('06') and bc.mabc in ('DH')  then 1 else 0 end) as YTDH,";
            sql += " sum(case when dt.manhom not in ('01','02','06') and bc.mabc in ('DH')  then 1 else 0 end) as DHkhac,";
            //sql += " sum(case when bc.mabc in ('CD')  then 1 else 0 end) as CDang,";
            sql += " sum(case when dt.manhom in ('07') and bc.mabc in ('TH') then 1 else 0 end) as YSTH,";
            sql += " sum(case when dt.manhom in ('04') and bc.mabc in ('TH') then 1 else 0 end) as KTV,";
            sql += " sum(case when dt.manhom in ('02') and bc.mabc in ('TH') then 1 else 0 end) as DSTH,";
            sql += " sum(case when dt.manhom in ('06') and bc.mabc in ('TH') then 1 else 0 end) as YTTH,";
            sql += " sum(case when dt.manhom in ('08') and bc.mabc in ('TH') then 1 else 0 end) as NHSTH,";
            sql += " sum(case when dt.manhom not in ('02','04','06','07','08') and bc.mabc in ('TH') then 1 else 0 end) as THkhac,";
            sql += " sum(case when dt.manhom in ('06')  and bc.mabc in ('SH') then 1 else 0 end) as YTSH,";
            sql += " sum(case when dt.manhom in ('08') and bc.mabc in ('SH') then 1 else 0 end) as NHSSH,";
            sql += " sum(case when dt.manhom in ('03') and bc.mabc in ('SH') then 1 else 0 end) as Dta,";
            sql += " 0 as CBkhac,";
            sql += " sum(case when (mh.nhombc=2) then 1 else 0 end) as Tquy,";
            sql += " sum(case when (mh.nhombc =3) then 1 else 0 end) as Nquy";
            sql += " FROM " + user + ".llnv l inner join " + user + ".hopdong hd on l.manv=hd.manv";
            sql += " inner join " + user + ".bophan bp on  bp.madv=l.madv";
            sql += " left join " + user + ".dmcd cd on cd.ma=l.macd";
            sql += " left join " + user + ".bangcap bc on bc.mabangcap=l.td_nnghiep";
            sql += " left join " + user + ".mangach mn on hd.mangach=mn.ma";
            sql += " left join " + user + ".mahd mh on hd.loaihd=mh.mahd";
            sql += " left join " + user + ".ctdaotao dt on l.nnghiep=dt.mactdt";
            sql += " inner join " + user + ".max_lan ml on hd.manv=ml.manv";
            sql += " WHERE  (hd.ngaynghi is null or to_date(to_char(hd.ngaynghi,'dd/mm/yyyy'),'dd/mm/yyyy') > to_date('" + s_den + "','dd/mm/yyyy')) ";
            sql += " and to_date(to_char(hd.ngaydenct,'dd/mm/yyyy'),'dd/mm/yyyy')<=to_date('" + s_den + "','dd/mm/yyyy') ";
            sql += " and hd.lan=ml.lan";
            return sql;
        }

		private void Capnhat(DataSet ds,int i,int temp)
		{
			try
			{		
				int id=0,count =0;
				id =i;
				count =ds.Tables[0].Rows.Count;
				if( count> 0)
				{
					string upd="",ngay="";
					int c1=0,c2=0,c3=0,c4=0,c5=0,c6=0,c7=0,c8=0,c9=0,c10=0,c11=0,c12=0,c13=0,c14=0,c15=0,c16=0,c17=0,c18=0,c19=0,c20=0,c21=0,c22=0,c23=0;
					int ma=0;					
					foreach(DataRow dr in ds.Tables[0].Rows)
					{
                        ngay=dr["ngay"].ToString() != "" ? "to_date('"+dr["ngay"].ToString().Trim()+"','dd/mm/yyyy')" : "null";
						if(temp > 0)
							ma=temp;
						else
							ma =dr["mabaocao"].ToString().Trim()!="" ? int.Parse(dr["mabaocao"].ToString()):0;
						c1 =dr["tongso"].ToString().Trim()!="" ? int.Parse(dr["tongso"].ToString()):0;
						c2 =dr["tongnu"].ToString().Trim()!="" ? int.Parse(dr["tongnu"].ToString()):0;
						c3 =dr["gs"].ToString().Trim()!="" ? int.Parse(dr["gs"].ToString()):0;
						c4 =dr["ts"].ToString().Trim()!="" ? int.Parse(dr["ts"].ToString()):0;
						c5 =dr["ths"].ToString().Trim()!="" ? int.Parse(dr["ths"].ToString()):0;
						c6 =dr["CKII"].ToString().Trim()!="" ? int.Parse(dr["CKII"].ToString()):0;
						c7 =dr["CKI"].ToString().Trim()!="" ? int.Parse(dr["CKI"].ToString()):0;
						c8 =dr["BS"].ToString().Trim()!="" ? int.Parse(dr["bs"].ToString()):0;
						c9 =dr["dsdh"].ToString().Trim()!="" ? int.Parse(dr["dsdh"].ToString()):0;
						c10 =dr["dhkhac"].ToString().Trim()!="" ? int.Parse(dr["dhkhac"].ToString()):0;
						c11 =dr["ytdh"].ToString().Trim()!="" ? int.Parse(dr["ytdh"].ToString()):0;
						c12 =dr["ysth"].ToString().Trim()!="" ? int.Parse(dr["ysth"].ToString()):0;
						c13 =dr["ktv"].ToString().Trim()!="" ? int.Parse(dr["ktv"].ToString()):0;
						c14 =dr["dsth"].ToString().Trim()!="" ? int.Parse(dr["dsth"].ToString()):0;
						c15 =dr["ytth"].ToString().Trim()!="" ? int.Parse(dr["ytth"].ToString()):0;
						c16 =dr["nhsth"].ToString().Trim()!="" ? int.Parse(dr["nhsth"].ToString()):0;
						c17 =dr["thkhac"].ToString().Trim()!="" ? int.Parse(dr["thkhac"].ToString()):0;
						c18 =dr["ytsh"].ToString().Trim()!="" ? int.Parse(dr["ytsh"].ToString()):0;
						c19 =dr["nhssh"].ToString().Trim()!="" ? int.Parse(dr["nhssh"].ToString()):0;
						c20 =dr["dta"].ToString().Trim()!="" ? int.Parse(dr["dta"].ToString()):0;
						c21 =dr["cbkhac"].ToString().Trim()!="" ? int.Parse(dr["cbkhac"].ToString()):0;
						c22 =dr["tquy"].ToString().Trim()!="" ? int.Parse(dr["Tquy"].ToString()):0;
						c23 =dr["Nquy"].ToString().Trim()!="" ? int.Parse(dr["Nquy"].ToString()):0;
						upd= " insert into medibv.bieu_01 ( ";
						upd+=" id,ma,ngay,c01,c02,c03,c04,c05,c06,c07,c08,c09,c10,";
						upd+=" c11,c12,c13,c14,c15,c16,c17,c18,c19,c20,c21,c22,c23";
						upd+=")";         
						upd+=" values("+id+","+ma+","+ngay.Trim()+","+c1+","+c2+","+c3+",";
						upd+=" "+c4+","+c5+","+c6+","+c7+","+c8+","+c9+","+c10+",";
						upd+=" "+c11+","+c12+","+c13+","+c14+","+c15+","+c16+",";
						upd+=" "+c17+","+c18+","+c19+","+c20+","+c21+","+c22+","+c23+")";
						m.Execute_data(upd);
						
						//id+= 1;
					}					
				}				
			}
			catch
			{
			
			}
		}

        private void btn_thoat_Click(object sender, EventArgs e)
        {
            this.Close();
        }

        private void btn_ok_Click(object sender, EventArgs e)
        {
            s_tu = den.Text.Trim();
            s_tu1 = s_tu;
            s_den = den.Text.Trim();

            if (loaibc.SelectedIndex == 0)
            {
                s_tu = "01/01/" + nam.Value.ToString();
                //s_tu2 = "01/01/" + nam.Value.ToString();
                s_den = "31/12/" + nam.Value.ToString();
                switch (ky.SelectedIndex)
                {
                    case 0:
                        s_tu = "01/01/" + nam.Value.ToString();
                        s_den = "31/03/" + nam.Value.ToString();
                        break;
                    case 1:
                        s_tu = "01/04/" + nam.Value.ToString();
                        s_den = "30/06/" + nam.Value.ToString();
                        break;
                    case 2:
                        s_tu = "01/07/" + nam.Value.ToString();
                        s_den = "30/09/" + nam.Value.ToString();
                        break;
                    case 3:
                        s_tu = "01/10/" + nam.Value.ToString();
                        s_den = "31/12/" + nam.Value.ToString();
                        break;

                }
                s_tu1 = s_tu;
            }

            Thuchien();
            //=======================
            //f_Chuyen();

            //string sql = "select * from bieu_01 where userid=" + m_userid + " and to_date(to_char(ngay,'dd/mm/yyyy'),'dd/mm/yyyy') between to_date('" + s_tu + "','dd/mm/yyyy') and to_date('" + s_den + "','dd/mm/yyyy')  ";
            //DataSet d = m.get_data(sql);
            //if (m.get_data(sql).Tables[0].Rows.Count > 0)
            //    m.Execute_data("delete from bieu_01 where userid=" + m_userid + " and to_date(to_char(ngay,'dd/mm/yyyy'),'dd/mm/yyyy') between to_date('" + s_tu + "','dd/mm/yyyy') and to_date('" + s_den + "','dd/mm/yyyy')");
            MessageBox.Show("Chuyển thành công !", "Medisoft");
            //========================
        }
        private void f_Chuyen()
        {
            long id = 0;
            id = m.get_capid(10);
            sql = " select id,ma,to_char(ngay,'dd/mm/yyyy') as ngay,c01,c02,c03,c04,c05,c06,c07,c08,c09,c10,c11,c12,c13,c14,c15,c16,c17,c18,c19,c20,c21,c22,c23 from medibv.bieu_01";
            sql += " where userid=" + m_userid + " and to_date(to_char(ngay,'dd/mm/yyyy'),'dd/mm/yyyy') between to_date('" + s_tu + "','dd/mm/yyyy') and to_date('" + s_den + "','dd/mm/yyyy')";
            DataSet ds = m.get_data(sql);
            foreach (DataRow r in m.get_data(sql).Tables[0].Rows)
            {

                m.upd_bieu01(id, int.Parse(r["ma"].ToString()), r["ngay"].ToString().Trim(), int.Parse(r["c01"].ToString())
                    , int.Parse(r["c02"].ToString()), int.Parse(r["c03"].ToString()), int.Parse(r["c04"].ToString())
                    , int.Parse(r["c05"].ToString()), int.Parse(r["c06"].ToString()), int.Parse(r["c07"].ToString())
                    , int.Parse(r["c08"].ToString()), int.Parse(r["c09"].ToString()), int.Parse(r["c10"].ToString())
                    , int.Parse(r["c11"].ToString()), int.Parse(r["c12"].ToString()), int.Parse(r["c13"].ToString())
                    , int.Parse(r["c14"].ToString()), int.Parse(r["c15"].ToString()), int.Parse(r["c16"].ToString())
                    , int.Parse(r["c17"].ToString()), int.Parse(r["c18"].ToString()), int.Parse(r["c19"].ToString())
                    , int.Parse(r["c20"].ToString()), int.Parse(r["c21"].ToString()), int.Parse(r["c22"].ToString())
                    , int.Parse(r["c23"].ToString()), 999);
            }
        }
	}
}
